﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text;
using System.Web;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel; //xls格式(97-2003)
using NPOI.XSSF.UserModel; //xlsx格式(2007)
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Tzx.Utilities.Npoi
{
    /// <summary>
    /// Npoi导出辅助类
    /// </summary>
    public class NpoiExport
    {

        //NPOI导出Excel内容有问题,出现"部分内容有问题"的解决方法
        //https://www.cnblogs.com/liyhbk/p/14954689.html
        //同一个文件用WPS打开则正常,但我们不可能要求用户都用WPS,这不现实

        //方法一：
        //在获得 workbook.Write(ms) 生成的 MemoryStream 后，使用了 ms.GetBuffer() 返回文件内容，导致生成的 Excel 文件结尾处有大量的 00(空字节)，改为 ms.ToArray() 即可得到正常的文件了。
        //问题在于 window excel 冻结窗口只能设置一行, WPS可以设置多行
        //设置冻结窗口如下: 冻结第一行 sheet.createFreezePane(0, 1, 0, 1);

        //方法二：在响应头设置导出内容的长度(Web)

        //方法三:我的WinForm程序,升级 NPOI 和 SharpZipLib,NPOI v2.5.2 和 SharpZipLib v1.3.1.

        //方法四:需将ms.GetBuffer()改成ms.ToArray()即可
        //using (MemoryStream ms = new MemoryStream()){ 
        //workbook.Write(ms);
        //buff = ms.GetBuffer();
        //} 

        //sheet.setDisplayGridlines(false);//Sheet对象,设置不显示excel网格线

        #region 构造

        /// <summary>
        /// Npoi导出辅助类
        /// </summary>
        /// <param name="table">待导出的DataTable</param>
        /// <param name="isExcel2007">是否为Excel2007格式</param>
        public NpoiExport(DataTable table, bool isExcel2007)
        {
            if (table == null)
            {
                throw new ApplicationException("源表不能为空");
            }

            //
            this._sourceTable = table;
            this._isExcel2007 = isExcel2007;
        }

        /// <summary>
        /// Npoi导出辅助类,按模板文件导出,模板文件与导出文件格式要一样
        /// </summary>
        /// <param name="table">待导出的DataTable</param>
        /// <param name="excelTemplateStream">Excel模板</param>
        /// <param name="isExcel2007">是否为Excel2007格式</param>
        public NpoiExport(DataTable table, Stream excelTemplateStream, bool isExcel2007)
            : this(table, isExcel2007)
        {
            InitWorkbookTemplate(excelTemplateStream); //设置模板
        }

        /// <summary>
        /// 构造(默认导出Excel97-2003老格式,兼容)
        /// </summary>
        /// <param name="table">待导出的DataTable</param>
        public NpoiExport(DataTable table): this(table, false)
        {
            //
        }

        /// <summary>
        /// 源数据表
        /// </summary>
        public DataTable SourceTable
        {
            get { return _sourceTable; }
        }
        private DataTable _sourceTable;

        /// <summary>
        /// 是否为Excel2007格式
        /// </summary>
        protected bool IsExcel2007
        {
            get { return _isExcel2007; }
        }
        private bool _isExcel2007 = false;

        /// <summary>
        /// 是否按模板导出
        /// </summary>
        public bool IsByTemplate
        {
            get { return _isByTemplate; }
        }
        private bool _isByTemplate = false;

        /// <summary>
        /// Table列名_Table标题
        /// </summary>
        protected Dictionary<string, string> SourceTable_ColumnAndCaption
        {
            get
            {
                if (_sourceTable_ColumnAndCaption == null)
                {
                    if (SourceTable != null)
                    {
                        _sourceTable_ColumnAndCaption = new Dictionary<string, string>(StringComparer.CurrentCultureIgnoreCase);
                        foreach (DataColumn column in SourceTable.Columns)
                        {
                            string colName = string.Format("{0}", column.ColumnName).Trim();
                            string colCaption = string.Format("{0}", column.Caption).Trim();
                            string caption = (String.Compare(colName, colCaption, StringComparison.InvariantCulture) == 0)
                                ? colName
                                : colCaption;
                            _sourceTable_ColumnAndCaption.Add(colName, caption);
                        }
                    }
                }
                return _sourceTable_ColumnAndCaption;
            }
        }
        private Dictionary<string, string> _sourceTable_ColumnAndCaption;

        /// <summary>
        /// Table列名_Excel列号
        /// </summary>
        protected Dictionary<string, int> SourceTable_ColumnAndExcelColumnIndex
        {
            get
            {
                if (_sourceTable_ColumnAndExcelColumnIndex == null)
                {
                    if (SourceTable != null)
                    {
                        _sourceTable_ColumnAndExcelColumnIndex = new Dictionary<string, int>(StringComparer.CurrentCultureIgnoreCase);
                        foreach (DataColumn column in SourceTable.Columns)
                        {
                            string colName = string.Format("{0}", column.ColumnName).Trim();
                            _sourceTable_ColumnAndExcelColumnIndex.Add(colName, -1); //默认全部设置-1,即不存在,需要时再设置
                        }
                    }
                }
                return _sourceTable_ColumnAndExcelColumnIndex;
            }
        }
        private Dictionary<string, int> _sourceTable_ColumnAndExcelColumnIndex;

        #endregion

        #region 属性

        /// <summary>
        /// 是否显示Excel网络线
        /// </summary>
        public bool DisplayGridlines { get; set; }

        /// <summary>
        /// 是否画边框和标题底色
        /// </summary>
        public bool DrawBorderAndBackground { get; set; }

        #endregion

        #region 工作簿(是新创建,还是基于模板工作簿)

        /// <summary>
        /// Excel流转换成Excel工作簿(在初始化构造时)
        /// </summary>
        /// <param name="excelStream"></param>
        private void InitWorkbookTemplate(Stream excelStream)
        {
            //1.Excel流
            if (excelStream == null)
            {
                throw new ApplicationException("模板文件流为空");
                return;
            }

            //2.读取原Excel文件
            //_excelWorkbook = new HSSFWorkbook(excelStream);
            //是Excel97_2003还是Excel2007
            if (!IsExcel2007)
            {
                _excelWorkbook = new HSSFWorkbook(excelStream);//excel97-2003
            }
            else
            {
                _excelWorkbook = new XSSFWorkbook(excelStream);//excel2007
                //BUG:Wrong Local header signature: 0xE011CFD0
            }

            //
            if (_excelWorkbook.NumberOfSheets == 0)
            {
                throw new ApplicationException("模板文件没有Sheet工作表");
                return;
            }

            //3.读取第一个sheet
            if (_excelWorkbook.NumberOfSheets == 0)
            {
                throw new ApplicationException("模板文件没有Sheet工作表总数为0");
                return;
            }
            ISheet sheet = _excelWorkbook.GetSheetAt(0); //模板文件第一个sheet
            if (sheet == null)
            {
                throw new ApplicationException("模板文件第一个Sheet工作表为空");
                return;
            }
            _isByTemplate = true;//基于模板导出
        }

        /// <summary>
        /// Excel工作簿(是模板文件导出,或新建空白文件无格式导出)
        /// </summary>
        public IWorkbook ExcelWorkbook
        {
            get
            {
                //工作簿
                if (_excelWorkbook == null)
                {
                    //不是基于模板文件,则新建一个工作簿
                    //_excelWorkbook = new HSSFWorkbook(); 
                    
                    //是Excel97_2003还是Excel2007
                    if (!IsExcel2007)
                    {
                        _excelWorkbook = new HSSFWorkbook(); //Excel97-2003
                    }
                    else
                    {
                        _excelWorkbook = new XSSFWorkbook();//Excel2007
                    }
                }

                //工作表
                ISheet sheet = null; //工作簿主sheet
                if (_excelWorkbook.NumberOfSheets > 0)
                {
                    sheet = _excelWorkbook.GetSheetAt(0);
                }
                if (sheet == null)
                {
                    sheet = _excelWorkbook.CreateSheet();
                    sheet.SetActive(true);
                }

                //修改sheet名称
                int idex = 0; //sheet的索引
                _excelWorkbook.SetSheetName(idex, "导出" + DateTime.Now.ToString("yyyyMMddHHmm"));

                //
                return _excelWorkbook;
            }
        }
        private IWorkbook _excelWorkbook;

        #endregion

        #region 导出参数(开始行号列号,只导出选定列,排序列,图片列,行高,列宽)

        /// <summary>
        /// 数据导出开始列Index,默认从0即A列开始,不是直接用column.Ordinal,因为有选择导出列的情况
        /// </summary>
        public int StartColumnIndex
        {
            get { return _startColumnIndex; }
            set { _startColumnIndex = value; }
        }
        private int _startColumnIndex = 0;

        /// <summary>
        /// 导出开始行-标题行Index,默认是0即从1行开始
        /// </summary>
        public int StartRowIndexCaption
        {
            get { return _startRowIndexCaption; }
            set
            {
                _startRowIndexCaption = value;
                _startRowIndexData = _startRowIndexCaption + 1;//默认数据开始行,在标题行的下一行
            }
        }
        private int _startRowIndexCaption = 0; //默认0

        /// <summary>
        /// 导出开始行-数据行Index,默认是1即从2行开始(即标题行Index+1)
        /// </summary>
        public int StartRowIndexData
        {
            get { return _startRowIndexData; }
            set { _startRowIndexData = value; }
        }
        private int _startRowIndexData = 1; //默认1

        /// <summary>
        /// 只导出这些选择的列
        /// </summary>
        public List<string> OnlyColumnName
        {
            get
            {
                if (_onlyColumnName == null)
                {
                    _onlyColumnName = new List<string>();
                }
                return _onlyColumnName;
            }
            set { _onlyColumnName = value; }
        }
        private List<string> _onlyColumnName;

        /// <summary>
        /// Excel跳过列,即Excel模板文件保持原样,适用按模板导出
        /// </summary>
        public int[] ExcelSkipColumns
        {
            get
            {
                if (_excelSkipColumns == null)
                {
                    _excelSkipColumns = new int[0];
                }
                return _excelSkipColumns;
            }
            set { _excelSkipColumns = value; }
        }
        private int[] _excelSkipColumns;

        /// <summary>
        /// 跳过标题行,即是否输出Excel标题行,适用按模板导出
        /// </summary>
        public bool ExcelSkipRowCaption { get; set; }

        /// <summary>
        /// 排序列,只导出选择列(设置DataTable列顺序,以便导出的顺序是可控的)
        /// </summary>
        public bool SortDataTableColumn()
        {
            if (OnlyColumnName != null)
            {
                return NpoiUtilities.SetDataTableSort(SourceTable, OnlyColumnName.ToArray());
            }
            return false;
        }

        /// <summary>
        /// 是图片的列名(byte[]类型)
        /// </summary>
        public string[] ImageColumnNames
        {
            get
            {
                if (_imageColumnNames == null)
                {
                    _imageColumnNames = new string[0];
                }
                return _imageColumnNames;
            }
            set { _imageColumnNames = value; }
        }
        private string[] _imageColumnNames;

        /// <summary>
        /// Excel行高(Excel里的Height单位是"点",乘20倍)
        /// </summary>
        public short? ExcelRowHeight { get; set; }

        /// <summary>
        /// Excel列宽键值对(列名_列宽)(Excel里的Width单位是"点",乘256倍)
        /// Excel行高所使用单位为磅,
        /// Excel列宽使用单位为0.1英寸
        /// </summary>
        public Dictionary<string, double> ExcelColumnWidth
        {
            get
            {
                if (_excelColumnWidth == null)
                {
                    _excelColumnWidth = new Dictionary<string, double>(StringComparer.CurrentCultureIgnoreCase);
                }
                return _excelColumnWidth;
            }
            set { _excelColumnWidth = value; }
        }
        private Dictionary<string, double> _excelColumnWidth;

        #endregion

        #region 单元格的格式和样式

        /// <summary>
        /// 常规格式
        /// ICellStyle styleGeneral = ExcelWorkbook.CreateCellStyle();
        /// styleGeneral.DataFormat = 0;//"General";
        /// </summary>
        protected ICellStyle StyleGeneral
        {
            get
            {
                if (_styleGeneral == null)
                {
                    _styleGeneral = ExcelWorkbook.CreateCellStyle();
                    _styleGeneral.DataFormat = 0;//"General";
                    _styleGeneral.VerticalAlignment = VerticalAlignment.Center;//垂直居中

                    //画边框(通用)
                    if (DrawBorderAndBackground)
                    {
                        _styleGeneral.BorderBottom = BorderStyle.Thin;
                        _styleGeneral.BorderLeft = BorderStyle.Thin;
                        _styleGeneral.BorderTop = BorderStyle.Thin;
                        _styleGeneral.BorderRight = BorderStyle.Thin;
                    }
                }
                return _styleGeneral;
            }
        }
        private ICellStyle _styleGeneral = null;

        /// <summary>
        /// 日期样式
        /// <remarks>
        /// ICellStyle styleDate = ExcelWorkbook.CreateCellStyle();
        /// IDataFormat format = ExcelWorkbook.CreateDataFormat();
        /// styleDate.DataFormat = format.GetFormat("yyyy-mm-dd");
        /// </remarks>
        /// </summary>
        protected ICellStyle StyleDate
        {
            get
            {
                if (_styleDate == null)
                {
                    //_styleDate = ExcelWorkbook.CreateCellStyle();
                    //IDataFormat format = ExcelWorkbook.CreateDataFormat();
                    //_styleDate.DataFormat = format.GetFormat("yyyy-mm-dd");

                    //
                    _styleDate = ExcelWorkbook.CreateCellStyle();
                    _styleDate.CloneStyleFrom(StyleGeneral);
                    IDataFormat format = ExcelWorkbook.CreateDataFormat();
                    _styleDate.DataFormat = format.GetFormat("yyyy-mm-dd");
                }
                return _styleDate;
            }
        }
        private ICellStyle _styleDate = null;

        /// <summary>
        /// 标题样式
        /// </summary>
        protected ICellStyle StyleHead
        {
            get
            {
                if (_styleHead == null)
                {
                    _styleHead = ExcelWorkbook.CreateCellStyle();
                    _styleHead.CloneStyleFrom(StyleGeneral);

                    //画边框
                    if (DrawBorderAndBackground)
                    {
                        _styleHead.Alignment = HorizontalAlignment.Center; // 居中
                        //_styleHead.FillBackgroundColor = IndexedColors.Automatic.Index; //这个是坑,FillBackgroundColor不是背景色,FillForegroundColor才是
                        _styleHead.FillForegroundColor = IndexedColors.Grey25Percent.Index; //FillForegroundColor,这个才是背景颜色
                        _styleHead.FillPattern = FillPattern.SolidForeground;
                    }
                }
                return _styleHead;
            }
        }
        private ICellStyle _styleHead = null;

        #endregion

        #region 写Excel - 标题行

        /// <summary>
        /// 导出Excel标题行(DataTable => Excel对象)
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <returns></returns>
        private Dictionary<string, string> DataTableToExcel_RowCaption(ISheet sheet)
        {
            //标题行
            int rowIndex = StartRowIndexCaption;//开始行(标题)
            IRow headerRow;
            if (sheet.GetRow(rowIndex) == null)
            {
                headerRow = sheet.CreateRow(rowIndex);
            }
            else
            {
                headerRow = sheet.GetRow(rowIndex);
            }

            //
            int exportColumnIndex = StartColumnIndex; //Table列Index
            int excelColumnIndex = exportColumnIndex; //Excel列
            string[] onlyColumnName = OnlyColumnName.ToArray(); //仅导出这些列(Caption或ColumnName匹配)
            foreach (KeyValuePair<string, string> kv in SourceTable_ColumnAndCaption)
            {
                //只导出指定列(标题)
                if (OnlyColumnName != null && OnlyColumnName.Count > 0)
                {
                    bool inColumnName = Array.IndexOf(onlyColumnName, kv.Key) != -1;
                    bool inColumnCaption = Array.IndexOf(onlyColumnName, kv.Value) != -1;
                    if (!inColumnName && !inColumnCaption)
                    {
                        continue; //导出列不存在(列名和标题)
                    }
                }

                //导出
                string excelCaption = kv.Value; //Excel标题行
                if (string.IsNullOrEmpty(excelCaption))
                {
                    excelCaption = kv.Key;
                }

                //设置标题--------------------------------------------------------------------------------
                //Excel跳出列
                while (Array.IndexOf(ExcelSkipColumns, excelColumnIndex) > -1)
                {
                    excelColumnIndex += 1;//Excel跳出列(标题)
                }
                //Debug.WriteLine(excelCaption + "=" + excelColumnIndex.ToString());

                //写Excel单元格
                ICell cell;
                if (headerRow.GetCell(excelColumnIndex) == null)
                {
                    cell = headerRow.CreateCell(excelColumnIndex);
                }
                else
                {
                    cell = headerRow.GetCell(excelColumnIndex);
                }

                cell.CellStyle = StyleHead; //标题行样式

                //不跳过标题行,才写入Excel
                if (!ExcelSkipRowCaption)
                {
                    cell.SetCellValue(excelCaption); //写入Excel
                }

                //Table列对应Excel列
                if (SourceTable_ColumnAndExcelColumnIndex.ContainsKey(kv.Key))
                {
                    SourceTable_ColumnAndExcelColumnIndex[kv.Key] = excelColumnIndex;
                }
                else
                {
                    SourceTable_ColumnAndExcelColumnIndex.Add(kv.Key, excelColumnIndex);
                }

                //
                excelColumnIndex += 1;//Excel跳出列
                //--------------------------------------------------------------------------------

                //列宽
                if (ExcelColumnWidth.Count > 0)
                {
                    string colName = kv.Key;
                    string colCaption = kv.Key;
                    bool isColName = ExcelColumnWidth.ContainsKey(colName);
                    bool isColCaption = ExcelColumnWidth.ContainsKey(colCaption);
                    if (isColName || isColCaption)
                    {
                        int point = (int) (ExcelColumnWidth[colName]*256.00);
                        sheet.SetColumnWidth(exportColumnIndex, point); //宽度 (Excel里的单位是"点",乘256倍)
                    }
                }

                //
                exportColumnIndex += 1;
            }//foreach

            return SourceTable_ColumnAndCaption;
        }

        /// <summary>
        /// 设置替换DataTable标题Caption
        /// </summary>
        /// <param name="colNameSs">列名</param>
        /// <param name="captionSs">新列标题</param>
        /// <returns></returns>
        public bool SetCaption(string[] colNameSs, string[] captionSs)
        {
            if (_sourceTable == null)
            {
                return false;
            }
            return NpoiUtilities.SetDataTableCaption(_sourceTable, colNameSs, captionSs);
        }

        #endregion

        #region 写Excel - 数据行

        /// <summary>
        /// 导出Excel(DataTable => Excel流)
        /// </summary>
        /// <returns></returns>
        public MemoryStream DataTableToExcelStream()
        {
            if (SourceTable == null)
            {
                return null;
            }

            //导出
            bool ok;
            if (_isByTemplate)
            {
                bool template_ok = DataTableToExcel_SetTemplate();
            }
            ok = DataTableToExcel(); //核心方法,Excel单元格赋值
            
            //
            if (!ok)
            {
                return null;
            }

            //设置Excel合并单元格
            if (!string.IsNullOrEmpty(mergeCell_PrimaryColumnName))
            {
                foreach (KeyValuePair<string, int> kvMerge in SourceTable_ColumnAndExcelColumnIndex)
                {
                    string columnName = kvMerge.Key;
                    string exist = mergeCell_ColumnNames.Find(
                        delegate(string s)
                        {
                            return String.Compare(s, columnName, StringComparison.InvariantCultureIgnoreCase) == 0;
                        });
                    if (exist == null) continue;

                    int columnIndex = kvMerge.Value; //列
                    //SetCellRangeAddress(ExcelWorkbook.GetSheetAt(0), 1, 40, columnIndex, columnIndex);//测试,合并单元格
                    foreach (KeyValuePair<int, int> kvRowIndex in mergeCell_TableRowBeginEndIndex)
                    {
                        int excel_row1 = kvRowIndex.Key + StartRowIndexData;
                        int excel_row2 = kvRowIndex.Value + StartRowIndexData;
                        SetCellRangeAddress(ExcelWorkbook.GetSheetAt(0), excel_row1, excel_row2, columnIndex,columnIndex); //合并单元格
                    }
                }
            }

            #region 导出为流

            //导出为流
            //MemoryStream ms = new MemoryStream();
            //ExcelWorkbook.Write(ms);
            //ms.Flush();
            //ms.Position = 0; //有BUG:导出Excel2007格式时(XSSFWorkbook),无法访问已关闭的流,而HSSFWorkbook没问题.
            ////workbook = null;
            //return ms;

            NpoiMemoryStream ms = new NpoiMemoryStream();
            ms.AllowClose = false;
            ExcelWorkbook.Write(ms);//Web和WinForm
            ms.Flush();
            ms.Position = 0; //有BUG:导出Excel2007格式时(XSSFWorkbook),无法访问已关闭的流,而HSSFWorkbook没问题.
            //workbook = null;
            ms.AllowClose = true;
            return ms;

            #endregion

            #region 导出为流BUG修复

            //原文链接：https://blog.csdn.net/eit520/article/details/53231642
            //导出Excel2007格式时(XSSFWorkbook),无法访问已关闭的流,而Excel97~2003格式(HSSFWorkbook)没问题.
            //XSSFWorkbook,出现这个错误,是因为在使用book.Write方法后,会关闭流,这样导致再次使用Respons输出流的时候就出错了
            //解决这个问题,需要从MemoryStream派生一个新类,专给NPOI用,解决流关闭的问题.
            /*
             * //新建类 重写Npoi流方法
             * public class NpoiMemoryStream : MemoryStream
             * {
             *     public NpoiMemoryStream()
             *     {
             *         AllowClose = true;
             *     }
             *     public bool AllowClose { get; set; }
             *     public override void Close()
             *     {
             *         if (AllowClose)
             *         base.Close();
             *     }
             * }
             * 
             * 
             * //导出Excel文件的方法
             *  var ms = new NpoiMemoryStream();
             *  ms.AllowClose = false;  //增加的代码
             *  workbook.Write(ms);
             *  ms.Flush();
             *  ms.Seek(0, SeekOrigin.Begin);
             *  ms.AllowClose = true;  //增加的代码
             *  
             *  HttpContext curContext = HttpContext.Current;
             *  curContext.Response.ContentType = "application/vnd.ms-excel";
             *  curContext.Response.ContentEncoding = Encoding.UTF8;
             *  curContext.Response.Charset = "";
             *  curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
             *  long fileSize = ms.Length;
             *  curContext.Response.AddHeader("Content-Length", fileSize.ToString());
             *  curContext.Response.BinaryWrite(ms.GetBuffer());
             *  curContext.Response.End();
             */
            #endregion

        }

        /// <summary>
        /// 导出Excel(核心方法)
        /// </summary>
        /// <returns></returns>
        private bool DataTableToExcel()
        {
            if (SourceTable == null)
            {
                return false;
            }
            if (ExcelWorkbook == null)
            {
                return false;
            }

            //工作表
            ISheet sheet = ExcelWorkbook.GetSheetAt(0);

            //工作表打印设置
            if (!_isByTemplate)
            {
                //templateExcelSheet.PrintSetup.Landscape = true;//横向
                //templateExcelSheet.PrintSetup.NoOrientation
                //templateExcelSheet.PrintSetup.Notes
                //templateExcelSheet.PrintSetup.UsePage
                sheet.PrintSetup.FitWidth = sheet.PrintSetup.FitHeight = 0; //使固定宽度和高度无效
                sheet.PrintSetup.Scale = 100; //缩放比例100%
                sheet.PrintSetup.PaperSize = 9; //9; //PaperSize.A4
                //templateExcelSheet.PrintSetup.ValidSettings = true;
            }

            //标题行(列名,标题)
            Dictionary<string, string> headerColumnNameCaption = DataTableToExcel_RowCaption(sheet);

            // handling value.
            int rowIndex = StartRowIndexData; //开始行(数据)

            //创建一个"绘画器",这个绘画器用于所有的图片写入.
            //请注意,是所有的图片,不可一张图片创建一个!否则将导致没有图片
            //HSSFPatriarch patriarch = templateExcelSheet.CreateDrawingPatriarch();
            //HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();//是Excel97_2003
            //XSSFDrawing patriarch = sheet.CreateDrawingPatriarch();//是Excel2007

            #region xls97还是xlsx2007,CreatePicture

            HSSFPatriarch patriarch97 = null;
            XSSFDrawing patriarch2007 = null;
            if (!IsExcel2007)
            {
                patriarch97 = (HSSFPatriarch) sheet.CreateDrawingPatriarch(); //是Excel97_2003
            }
            else
            {
                patriarch2007 = (XSSFDrawing) sheet.CreateDrawingPatriarch(); //是Excel2007
            }

            #endregion

            string[] onlyColumnName = OnlyColumnName.ToArray();

            #region 导出

            //导出行
            foreach (DataRow row in SourceTable.Rows)
            {
                //获取行或创建行
                IRow dataRow = null;

                dataRow = sheet.GetRow(rowIndex);
                if (dataRow == null)
                {
                    dataRow = sheet.CreateRow(rowIndex);
                }
                if (dataRow == null)
                {
                    continue;
                }

                //行高
                if (ExcelRowHeight.HasValue)
                {
                    dataRow.HeightInPoints = ExcelRowHeight.Value; //高度(HeightInPoints=点?Height=像素?)
                }

                //数据导出开始列(从0开始)
                int exportColumnIndex = StartColumnIndex; //列Index
                int excelColumnIndex = exportColumnIndex; //Excel写入列

                #region 数据行

                //遍历DataTable的列
                foreach (DataColumn column in SourceTable.Columns)
                {
                    //只导出指定列
                    if (OnlyColumnName != null && OnlyColumnName.Count > 0)
                    {
                        bool inColumnName = Array.IndexOf(onlyColumnName, column.ColumnName) != -1; //Key
                        bool inColumnCaption =
                            Array.IndexOf(onlyColumnName, headerColumnNameCaption[column.ColumnName]) != -1;
                        if (!inColumnName && !inColumnCaption)
                        {
                            continue; //跳出不导
                        }
                    }

                    //是否为用户指定的图片列
                    bool isImageCol = (
                        Array.IndexOf(ImageColumnNames, column.ColumnName) > -1
                        ||
                        Array.IndexOf(ImageColumnNames, column.ColumnName) > -1
                        );

                    Type[] integerArray = {typeof (Int16), typeof (Int32), typeof (Int64), typeof (Byte)};
                    Type[] decimalsArray = {typeof (Decimal), typeof (Double), typeof (Single)};

                    //Excel跳出列
                    while (Array.IndexOf(ExcelSkipColumns, excelColumnIndex) > -1)
                    {
                        excelColumnIndex += 1; //Excel跳出列(普通导出)
                    }

                    //
                    ICell dataCell = dataRow.GetCell(excelColumnIndex); //excelColumnIndex,exportColumnIndex
                    if (dataCell == null)
                    {
                        dataCell = dataRow.CreateCell(excelColumnIndex); //excelColumnIndex,exportColumnIndex
                    }
                    //excelColumnIndex += 1;//Excel跳出列

                    //格式
                    if (!_isByTemplate)
                    {
                        //不是基于模板
                        dataCell.CellStyle = StyleGeneral; //格式化显示
                    }

                    //导出

                    //0.NULL值
                    if (column == null || column.DataType == null || row.IsNull(column) || row[column] == DBNull.Value)
                    {
                        dataCell.SetCellValue(string.Empty);
                        //exportColumnIndex += 1; //导出列Index
                        //continue; //跳出
                    }
                        //1.数字型(整数)
                    else if (Array.IndexOf(integerArray, column.DataType) != -1)
                    {
                        int i = 0;
                        int.TryParse(row[column].ToString(), out i);
                        dataCell.SetCellValue(i);
                        //if (!_isByTemplate) //不是基于模板
                        //{
                        //    dataCell.CellStyle = StyleGeneral; //格式化显示
                        //}
                    }
                        //2.小数型
                    else if (Array.IndexOf(decimalsArray, column.DataType) != -1)
                    {
                        decimal d = 0.00m;
                        Decimal.TryParse(row[column].ToString(), out d);
                        dataCell.SetCellValue((double) d);
                        //if (!_isByTemplate) //不是基于模板
                        //{
                        //    dataCell.CellStyle = StyleGeneral; //格式化显示
                        //}
                    }
                        //3.日期型
                    else if (column.DataType == typeof (DateTime))
                    {
                        DateTime dateV;
                        if (DateTime.TryParse(row[column].ToString(), out dateV))
                        {
                            dataCell.SetCellValue(dateV);
                            if (!_isByTemplate)
                            {
                                //不是基于模板,日期格式
                                dataCell.CellStyle = StyleDate; //格式化显示日期
                            }
                        }
                        else
                        {
                            dataCell.SetCellValue(string.Empty);
                        }
                    }
                        //4.图片(byte[])
                    else if (column.DataType == typeof (byte[]) && isImageCol)
                    {
                        //插入图片到 Excel，并返回一个图片的标识
                        int pictureIndex = ExcelWorkbook.AddPicture((byte[]) row[column], PictureType.JPEG);

                        #region xls97还是xlsx2007,HSSFClientAnchor或XSSFClientAnchor

                        HSSFClientAnchor anchor97 = null;
                        XSSFClientAnchor anchor2007 = null;

                        //创建图片的位置
                        if (!IsExcel2007)
                        {
                            anchor97 = new HSSFClientAnchor(
                                8, 2, //上左 到 上右 的位置,是基于下面的行列位置82
                                0, 0, //下左 到 下右 的位置,是基于下面的行列位置00
                                excelColumnIndex, rowIndex, //exportColumnIndex, rowIndex,//excelColumnIndex,rowIndex,
                                excelColumnIndex + 1, rowIndex + 1
                                //exportColumnIndex + 1, rowIndex + 1//excelColumnIndex, rowIndex + 1 
                                );
                        }
                        else
                        {
                            anchor2007 = new XSSFClientAnchor(
                                8, 2, //上左 到 上右 的位置,是基于下面的行列位置82
                                0, 0, //下左 到 下右 的位置,是基于下面的行列位置00
                                excelColumnIndex, rowIndex, //exportColumnIndex, rowIndex,//excelColumnIndex,rowIndex,
                                excelColumnIndex + 1, rowIndex + 1
                                //exportColumnIndex + 1, rowIndex + 1//excelColumnIndex, rowIndex + 1 
                                );
                        }

                        #endregion

                        #region xls97还是xlsx2007,CreatePicture

                        //图片输出的位置这么计算的:
                        //假设我们要将图片放置于第 5(E) 列的第 2 行 
                        //对应索引为是 4 : 1(默认位置)
                        //放置的位置就等于(默认位置)到(默认位置各自加上一行,一列)
                        if (!IsExcel2007)
                        {
                            patriarch97.CreatePicture(anchor97, pictureIndex); //使用绘画器绘画图片
                        }
                        else
                        {
                            //Excel2007
                            IPicture picture = patriarch2007.CreatePicture(anchor2007, pictureIndex);
                            picture.Resize(1.05, 1.10); //
                        }

                        #endregion

                        dataCell.SetCellValue(""); //设置一个空白,以免前列溢出到本列
                    }
                    else
                    {
                        //5.其它
                        dataCell.SetCellValue(row[column].ToString());
                        //if (!_isByTemplate) //不是基于模板
                        //{
                        //    dataCell.CellStyle = StyleGeneral; //格式化显示
                        //}
                    }

                    //
                    exportColumnIndex += 1; //导出列Index
                    excelColumnIndex += 1; //Excel跳出列
                } //foreach(遍历DataTable的列)

                #endregion

                rowIndex++;

            } //foreach(导出行)

            #endregion

            #region 样式

            //设置不显示excel网格线
            sheet.DisplayGridlines = DisplayGridlines;

            #endregion

            //
            return true;
        }

        /// <summary>
        /// 导出Excel(按模板文件)
        /// </summary>
        /// <returns></returns>
        private bool DataTableToExcel_SetTemplate()
        {
            //工作表
            ISheet sheet = ExcelWorkbook.GetSheetAt(0);

            // handling value.
            int rowIndex = StartRowIndexData; //开始行(数据)

            //数据总行数
            int rowCount = SourceTable.Rows.Count;
            sheet.ShiftRows(rowIndex, sheet.LastRowNum, rowCount, true, false); //在前方插入,模板行被推后
            IRow rowSource = sheet.GetRow(rowIndex + rowCount); //原模板行
            if (rowSource == null)
            {
                rowSource = sheet.CreateRow(rowIndex + rowCount); //原模板行
            }

            //导出行
            foreach (DataRow rowData in SourceTable.Rows)
            {
                //获取行或创建行
                IRow rowNew = sheet.GetRow(rowIndex);
                if (rowNew == null)
                {
                    rowNew = sheet.CreateRow(rowIndex);
                }

                //行高
                if (ExcelRowHeight.HasValue)
                {
                    rowNew.HeightInPoints = ExcelRowHeight.Value; //高度(HeightInPoints=点?Height=像素?)
                }
                else
                {
                    rowNew.Height = rowSource.Height;
                }

                //数据导出开始列(从0开始)
                int excelColumnIndex = StartColumnIndex; //Excel写入列

                //列:遍历Excel全部列,设置格式
                for (int colIndex = StartColumnIndex; colIndex < rowSource.LastCellNum; colIndex++)
                {
                    //
                    ICell cellNew = rowNew.GetCell(excelColumnIndex);
                    if (cellNew == null)
                    {
                        cellNew = rowNew.CreateCell(excelColumnIndex);
                    }

                    //模板单元格
                    ICell cellSource = rowSource.GetCell(cellNew.ColumnIndex);
                    if (cellSource == null)
                    {
                        cellSource = rowSource.CreateCell(cellNew.ColumnIndex);
                    }
                    cellNew.CellStyle = cellSource.CellStyle; //样式一样
                    
                    //cellNew.SetCellType(cellSource.CellType);//数据类型一样//设置数据类型,Excel会出错

                    //如果是公式列
                    if (cellSource.CellType == CellType.Formula)
                    {
                        //cellNew.SetCellValue("公式");
                        cellNew.SetCellValue(string.Empty);
                        //公式begin ------------------------------
                        string formula = cellSource.CellFormula;
                        string formula_new = RenewCopySameColumnCellFormula(formula, rowSource.RowNum + 1,rowNew.RowNum + 1);
                        cellNew.SetCellFormula(formula_new);
                        //公式end ------------------------------
                    }
                    else
                    {
                        cellNew.SetCellValue(string.Empty); //设置空值
                    }

                    //
                    //exportColumnIndex += 1; //导出列Index
                    excelColumnIndex += 1; //Excel列
                } //foreach 列

                rowIndex++;
            } //foreach行

            //重算
            sheet.ForceFormulaRecalculation = true;

            //
            return true;
        }

        #endregion

        #region 写Excel - 合并相同值且相邻的上下单元格

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowStart">开始行的索引</param>
        /// <param name="rowEnd">结束行的索引</param>
        /// <param name="colStart">开始列的索引</param>
        /// <param name="colEnd">结束列的索引</param>
        private static void SetCellRangeAddress(ISheet sheet, int rowStart, int rowEnd, int colStart, int colEnd)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 合并上下相同单元格的主键列
        /// </summary>
        private string mergeCell_PrimaryColumnName;

        /// <summary>
        /// 合并上下相同单元格的列
        /// </summary>
        private List<string> mergeCell_ColumnNames;

        /// <summary>
        /// 合并行索引(开始行号,结束行号)
        /// </summary>
        private Dictionary<int, int> mergeCell_TableRowBeginEndIndex;

        /// <summary>
        /// 设置合并列(此列中上下行值相同则合并单元格)
        /// </summary>
        /// <param name="primaryColumnName"></param>
        /// <param name="mergeCellColumnNames"></param>
        public void SetMergeCell(string primaryColumnName, List<string> mergeCellColumnNames)
        {
            //判断列名是否存在(主表主键列)
            this.mergeCell_PrimaryColumnName = null;
            if (SourceTable_ColumnAndCaption.ContainsKey(primaryColumnName))
            {
                this.mergeCell_PrimaryColumnName = primaryColumnName;
            }

            //判断列名是否存在(主表附属列)
            this.mergeCell_ColumnNames = new List<string>();
            foreach (string mergeColName in mergeCellColumnNames)
            {
                //this.mergeCell_ColumnNames = mergeCellColumnNames;
                if (SourceTable_ColumnAndCaption.ContainsKey(mergeColName))
                {
                    this.mergeCell_ColumnNames.Add(mergeColName);
                }
            }

            //遍历表格行,上下行的主键值对比
            mergeCell_TableRowBeginEndIndex = new Dictionary<int, int>();//合并列起始行号
            object oPrev = null;//遍历时上一个格的值
            int begin = 0;
            int end = 0;
            for (int i = 0; i < SourceTable.Rows.Count; i++)
            {
                if (i == 0)
                {
                    begin = i;
                    end = i;
                    oPrev = null;
                }

                object oCurrent = SourceTable.Rows[i][mergeCell_PrimaryColumnName]; //BUG:“name”参数不能为空。
                if (oPrev != null)
                {
                    if (oCurrent != oPrev)
                    {
                        if (begin != end)
                        {
                            mergeCell_TableRowBeginEndIndex.Add(begin, end);
                        }
                        begin = i;//更新
                    }
                }

                //准备下一次
                oPrev = oCurrent;
                end = i;
            } //for
            mergeCell_TableRowBeginEndIndex.Add(begin, end);//最后一个end

            //完成
        }

        #endregion

        #region 替换公式中行号数字

        /// <summary>
        /// 更新复制公式(同一列不同行,复制公式)
        /// </summary>
        private string RenewCopySameColumnCellFormula(string oldFormula, int oldRowIndex, int newRowIndex)
        {
            //检查
            oldFormula = string.Format("{0}", oldFormula).Trim().Replace(" ", string.Empty);
            if (oldRowIndex <= 0)
            {
                return null;
            }
            if (newRowIndex <= 0)
            {
                return null;
            }

            //正则表达式
            string pattern = pattern2.Replace("(" + HH + ")", "(" + oldRowIndex + ")");
            pattern = pattern.TrimStart('^').TrimEnd('$');//去除开头和结尾

            //正则表达式替换
            Regex r = new Regex(pattern, RegexOptions.IgnoreCase);
            string newFormula = r.Replace(oldFormula, delegate(System.Text.RegularExpressions.Match m)
            {
                return m.ToString().Replace(oldRowIndex.ToString(), newRowIndex.ToString());
            });

            //返回
            return newFormula;
        }

        /// <summary>
        /// Excel运算符大全:
        /// <![CDATA[
        ///1.算术:     +-*/%^
        ///2.比较:     = > < >= <= <> 
        ///3.文本连接: &
        ///4.引用:     冒号:连续引用  逗号,不连续引用 单个空格 (交叉引用)
        ///5.括号:           ()
        /// ]]>
        /// </summary>
        private const string pattern1 = "^[\\+\\-\\*\\/\\^\\>\\<\\=\\&\\:\\,\\ \\(\\)\\$]{0,}[a-zA-Z]{1,}[0-9]{1,}$";

        private const string HH = "行号";
        private const string pattern2 = "^[\\+\\-\\*\\/\\^\\>\\<\\=\\&\\:\\,\\ \\(\\)\\$]{0,}[a-zA-Z]{1,}(" + HH + "){1}$";

        #endregion

        #region 导出

        /// <summary>
        /// 保存输出到本机硬盘文件
        /// </summary>
        /// <param name="saveFileName"></param>
        public void ExportToWin(string saveFileName)
        {
            //BUG:Wrong Local header signature:0xE011CFD0。
            MemoryStream ms = DataTableToExcelStream(); //ExportToWin
            if (ms == null)
            {
                return;
            }

            NpoiUtilities.ExportToWin(saveFileName, ms);
        }

        /// <summary>
        /// Web导出
        /// </summary>
        /// <param name="downloadFileName">文件名</param>
        public void ExportToWeb(string downloadFileName)
        {
            HttpContext curContext = HttpContext.Current;
            MemoryStream ms = DataTableToExcelStream(); //ExportToWeb
            ExportToWeb(downloadFileName, ms, curContext);
        }

        /// <summary>
        /// Web导出:保存Excel流到Web的Response
        /// </summary>
        /// <param name="downloadFileName">输出文件名</param>
        /// <param name="httpStream">内存流</param>
        /// <param name="httpContext">当前Http上下文</param>
        public void ExportToWeb(string downloadFileName, MemoryStream httpStream, HttpContext httpContext)
        {
            //扩展名
            downloadFileName = string.Format("{0}", downloadFileName).Trim();
            if (!downloadFileName.EndsWith(".xlsx",true,CultureInfo.InvariantCulture) &&
                !downloadFileName.EndsWith(".xls", true, CultureInfo.InvariantCulture)
                )
            {
                string real_ext = IsExcel2007 ? ".xlsx" : ".xls"; //真实扩展名
                downloadFileName = downloadFileName + real_ext;
            }

            //设置编码和附件格式  
            httpContext.Response.ContentType = "application/vnd.httpStream-excel";
            httpContext.Response.ContentEncoding = Encoding.UTF8;
            httpContext.Response.Charset = "";
            httpContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(downloadFileName, Encoding.UTF8));

            if (httpStream != null)
            {
                //GetBuffer()导致Excel2007格式文件"发现...中的部分内容有问题",改用ToArray
                //httpContext.Response.BinaryWrite(httpStream.GetBuffer());
                httpContext.Response.BinaryWrite(httpStream.ToArray());
                
            }
            httpContext.Response.End();
        }

        #endregion

    }
}